﻿

CREATE proc [Projects].[GetProjectCases]
	@ProjectID	int = null,
	@Plz	nchar(10) = null,
	@StandortID int = null
as
begin
	set nocount on;

	declare @Telefon	tinyint = 1,
			@Mobil		tinyint = 2,
			@EMail		tinyint = 3

	--1. Resultset BusinessCase
	select	pj.ProjectID, 
			pj.Intern, 
			pj.ProjectStatus, 
			pj.MatchCode, 
			pj.PLZ, 
			st.Ort,
			st.Strasse,
			pj.StandortID, 
			rtrim(pj.Plz) + '-' + right('0000' + convert(varchar(4),pj.StandortID), 4) 'StandortCode',
			pj.CustomerSource, 
			pj.FirstContactDate, 
			pj.ImplementationDate, 
			pj.OfferDate, 
			pj.OrderDate, 
			pj.FinishDateProjected, 
			pj.FinishDateReal, 
			pj.CreateOn, 
			pj.CreateBy, 
			pj.LastChangedOn, 
			pj.LastChangedBy, 
			pj.FilePath, 
			pj.CalculationFileName,
			pj.Distance
	from	Projects.datProjects pj
			inner join datStandorte st on
				st.Plz = pj.PLZ
				and st.StandortID = pj.StandortID
	where	(pj.ProjectID = @ProjectID
				or @ProjectID is null)
			and (pj.PLZ = @Plz
				or @Plz is null)
			and (pj.StandortID = @StandortID
				or @StandortID is null)
	order by pj.MatchCode

	--2. Resultset Kontakte
	select	pj.ProjectID,
			c.ContactID,
			case when c.IsOrganisation != 0 then c.CompanyName else c.FirstName end 'Name1',
			case when c.IsOrganisation != 0 then c.CompanyNameAdditional else c.LastName end 'Name2',
			ct.ContactTypeID,
			ct.ContactType,
			aty.AddressType,
			p.LKZ,
			p.PLZ,
			p.Ort,
			a.Street,
			tel.Opportunity 'Telefon',
			mob.Opportunity 'Mobil',
			email.Opportunity 'EMail'
	from	Projects.datProjects pj
			inner join Projects.mnProjectContact pc on
				pc.ProjectID = pj.ProjectID
			inner join dbo.datContacts c on
				c.ContactID = pc.ContactID
			inner join Projects.domContactTypes ct on
				ct.ContactTypeID = pc.ContactType
			left join dbo.mnContactsAddress ca on
				ca.ContactID = c.ContactID
			left join datAddress a on
				a.AddressID = ca.AddressID
			left join dbo.domAddressType aty on
				aty.AddressTypeID = ca.AddressTypeID
			left join dbo.Postleitzahl p on
				p.EPLZ = a.ZipCodeID
			left join (
				select	co.ContactID,
						op.Opportunity, 
						op.IsActive,
						ROW_NUMBER() over (Partition by co.ContactID, co.OpportunityTypeID order by co.ContactID, co.OpportunityTypeID) 'RowNumber'
				from	dbo.mnContactsOpportunity co
						inner join datOpportunity op on
							op.OpportunityID = co.OpportunityID
							and co.OpportunityTypeID = @Telefon
							and op.IsActive = 1
				) tel on
				tel.ContactID = c.ContactID
				and tel.RowNumber = 1
			left join (
				select	co.ContactID,
						op.Opportunity, 
						op.IsActive,
						ROW_NUMBER() over (Partition by co.ContactID, co.OpportunityTypeID order by co.ContactID, co.OpportunityTypeID) 'RowNumber'
				from	dbo.mnContactsOpportunity co
						inner join datOpportunity op on
							op.OpportunityID = co.OpportunityID
							and co.OpportunityTypeID = @Mobil
							and op.IsActive = 1
				) mob on
				mob.ContactID = c.ContactID
				and mob.RowNumber = 1
			left join (
				select	co.ContactID,
						op.Opportunity, 
						op.IsActive,
						ROW_NUMBER() over (Partition by co.ContactID, co.OpportunityTypeID order by co.ContactID, co.OpportunityTypeID) 'RowNumber'
				from	dbo.mnContactsOpportunity co
						inner join datOpportunity op on
							op.OpportunityID = co.OpportunityID
							and co.OpportunityTypeID = @EMail
							and op.IsActive = 1
				) email on
				email.ContactID = c.ContactID
				and email.RowNumber = 1
	where	(pj.ProjectID = @ProjectID
				or @ProjectID is null)
			and (pj.PLZ = @Plz
				or @Plz is null)
			and (pj.StandortID = @StandortID
				or @StandortID is null)
	order by pj.MatchCode
end